package utils;

import java.nio.file.Paths;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import net.automatalib.words.Word;

public class DbHelper {

    private final String jdbcUrl;
    private Connection connection;

    // 构造函数接受数据库路径作为参数
    public DbHelper(Path dbPath) {
        this.jdbcUrl = "jdbc:sqlite:" + dbPath.toString();
    }

    // 打开数据库连接
    public void connect() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(jdbcUrl);
        }
    }

    // 关闭数据库连接
    public void disconnect() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 创建表
    private void __createTable(String tableName, String columns) {
        String sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + columns + ");";
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int getSessionID() throws Exception{
        String sql = "SELECT MAX(id) FROM qr";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                // 获取结果，如果表为空则返回0
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return 0; // 如果表为空，则返回0

    }

    // 获取单个值
    public void init() throws SQLException {
        // 定义 qr 表的列
        String qrColumns = "id INTEGER , query TEXT PRIMARY KEY , response TEXT NOT NULL, meta TEXT NOT NULL";
        try {
            __createTable("qr", qrColumns);  

         } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void insert(int id,String query, String response,String meta) throws SQLException{
         // 使用 PreparedStatement 安全插入数据到 qr 表
         String sql = "INSERT INTO qr(id, query, response, meta) VALUES(?, ?, ?, ?)";
         try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
             pstmt.setLong(1, id);
             pstmt.setString(2, query);
             pstmt.setString(3, response);
             pstmt.setString(4, meta);
             pstmt.executeUpdate();
         } catch (SQLException e) {
            e.printStackTrace();
        }
 
    }


public <I> Map<String, String> queryByQuery(Word<I> query) throws SQLException {
     String queryString = Utils.Word2String(query);
     return this.queryByQuery(queryString);
}

public Map<String, String> queryByQuery(String query) throws SQLException {
    // SQL 查询语句
    String sql = "SELECT id, query, response, meta FROM qr WHERE query = ?";
    
    // 创建返回结果的Map对象
    Map<String, String> result = new HashMap<>();

    // 使用 PreparedStatement 安全查询数据
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
        pstmt.setString(1, query);
        
        // 执行查询并获取结果集
        try (ResultSet rs = pstmt.executeQuery()) {
            // 检查是否查询到结果
            if (rs.next()) {
                // 从结果集中获取数据并填充到Map中
                result.put("id", rs.getString("id"));
                result.put("query", rs.getString("query"));
                result.put("response", rs.getString("response"));
                result.put("meta", rs.getString("meta"));
            } else {
                // 如果没有找到对应的记录，可以选择抛出异常或者返回null/空Map
                return null; // 或者 throw new NoSuchElementException("No record found with id: " + id);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        throw e; // 重新抛出捕获到的异常，以便调用者知道发生了什么
    }

    return result;
}


public void deleteByQuery(String query) throws SQLException {
    // SQL 删除语句
    String sql = "DELETE FROM qr WHERE query = ?";

    // 使用 PreparedStatement 安全地执行删除操作
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
        // 设置参数值
        pstmt.setString(1, query);

        // 执行更新操作（删除）
        int rowsAffected = pstmt.executeUpdate();
        System.out.println("Deleted " + rowsAffected + " row(s) where query = '" + query + "'");
    } catch (SQLException e) {
        e.printStackTrace();
        throw e; // 重新抛出异常以便调用者知道发生了什么
    }
}
    
    // 测试方法
    public static void main(String[] args) throws Exception {
        // 指定数据库路径
        Path dbPath = Paths.get("database.db"); // 替换为您的数据库路径

        // 创建 DbHelper 实例并测试基本功能
        DbHelper dbHelper = new DbHelper(dbPath);
        try {
            dbHelper.connect();

            dbHelper.init();
            dbHelper.insert(1, "1", "1","1");
            dbHelper.insert(2, "2", "1","1");
            dbHelper.insert(2, "3", "1","1");
            System.out.println(dbHelper.getSessionID());
            // dbHelper.insert("2", "2", "1","1");
            Map<String, String> result = dbHelper.queryByQuery("1");
            result.forEach((key, value) -> System.out.println("Key: " + key + ", Value: " + value));
            dbHelper.deleteByQuery("1");
            Map<String, String> result2 = dbHelper.queryByQuery("1");
            if(result2 != null){
                result2.forEach((key, value) -> System.out.println("Key: " + key + ", Value: " + value));    
            } else{
                System.out.println("delete successfully");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}